﻿/*== TSL_prc_GetDatabaseInfo =============================================================================
* Gets core info about the database configuration and the machine specs for
* the system housing the results repository.
* EXAMPLE:
EXEC	TSL_prc_GetDatabaseInfo
		@DatabaseName = N'LoadTest2010'
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_GetDatabaseInfo
		@DatabaseName NVARCHAR(255)
	AS
	BEGIN
		-- Get Database Version
		DECLARE @cmptlevel tinyint
		SELECT @cmptlevel = (SELECT cmptlevel from master.dbo.sysdatabases WHERE name = 'master')

		-- Get Recovery Model
		DECLARE @RecoveryModel NVARCHAR(255)
		SELECT @RecoveryModel = (SELECT CONVERT(NVARCHAR(255), DATABASEPROPERTYEX(name, 'Recovery')) AS 'Recovery Model' 
		FROM   master.dbo.sysdatabases WHERE name = @DatabaseName)

		-- Get number of cores
		DECLARE @CpuCount INT
		SELECT @CpuCount = (SELECT cpu_count FROM sys.dm_os_sys_info)

		-- Build the proper query for getting memory (column name changed in SQL 2012), Also add the results of
		-- the above queries so we get all info in a single resultset
		DECLARE @stmt nvarchar(MAX)
		IF(@cmptlevel = 110 OR @cmptlevel = 120) BEGIN
			SELECT @stmt = N'SELECT ' + CONVERT(NVARCHAR(255),@cmptlevel) + ' AS Version, '''
			+ @RecoveryModel + ''' AS [Recovery Model], ' 
			+ CONVERT(NVARCHAR(255),@CpuCount) 
			+ ' AS [Num Cores], (physical_memory_kb / 1048576) AS [Memory in GB] FROM sys.dm_os_sys_info' END
		ELSE BEGIN
			SELECT @stmt = N'SELECT ' + CONVERT(NVARCHAR(255),@cmptlevel) + ' AS Version, '''
			+ @RecoveryModel + ''' AS [Recovery Model], ' 
			+ CONVERT(NVARCHAR(255),@CpuCount) 
			+ ' AS [Num Cores], (physical_memory_in_bytes / 1073741824) AS [Memory in GB] FROM sys.dm_os_sys_info' END

		-- Get the info
		exec sp_executesql @stmt

		-- Get the Disk Info
		EXEC master.dbo.xp_fixeddrives

		-- Get the stats on the Load Test DB
		EXEC sp_helpdb @DatabaseName

		-- Get the stats on the Temp DB
		EXEC sp_helpdb tempdb

	END
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_GetDatabaseInfo TO PUBLIC
	GO

/*== TSL_prc_Get_TSL_Reporting_Version ===================================================================
* Returns the version of the TSL stored procedure set that is currently
* installed in the repository. This is used to see if the latest version
* of the procedures is installed.
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_Get_TSL_Reporting_Version
	AS
	BEGIN
		DECLARE @Version nvarchar(25)
		----- SET THE VERSION NUMBER HERE -----
		----- This should match exactly the version number in the reporting tool's config file
		SET @Version = '5.2.5.1 Beta'
		SELECT @Version
	END
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_Get_TSL_Reporting_Version TO PUBLIC
	GO

/*== TSL_prc_BiggestTablesByRows =========================================================================
* Returns a list of the largest five tables in the database based on the
* total number of rows in each table
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_BiggestTablesByRows
	AS
	BEGIN
		SELECT TOP 5 object_name(p.object_id) AS 'table name', CAST(SUM(rows) AS INT) AS [total rows] 
		FROM sys.partitions AS p
		INNER JOIN sys.objects AS o
			ON p.object_id = o.object_id
		WHERE o.type = 'U' and p.index_id in (0,1)
		GROUP BY p.object_id 
		ORDER BY [total rows] DESC
	END
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_BiggestTablesByRows TO PUBLIC
	GO





/*== TSL_prc_UpdateLoadTestCoreInfoTable =========================================================================
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_UpdateLoadTestCoreInfoTable AS	
	BEGIN
		---------------------------------------------------------------------------------
		DECLARE @RunId INT
		DECLARE @Qty INT -- value used at the end for parsing validation.
		DECLARE @Duration INT -- value used at the end for parsing validation.
		DECLARE myCursor CURSOR FOR
			SELECT LoadTestRunId FROM LoadTestRun 
			WHERE Outcome != 'InProgress'
			AND LoadTestRunId NOT IN (SELECT LoadTestRunId FROM TSL_LoadTestCoreInfo)
			
		OPEN myCursor
		FETCH NEXT FROM myCursor INTO @RunId
		WHILE @@FETCH_STATUS = 0
			BEGIN	
				---------------------------------------------------------------------------------
				-- Core Summary Info
				---------------------------------------------------------------------------------
				INSERT INTO TSL_LoadTestCoreInfo (LoadTestRunId)
					SELECT @RunId
				
				-- Set the parsing failed flag to TRUE immediately. Set it to false after all parsing completes
				UPDATE TSL_LoadTestCoreInfo	SET FLAG_LoadTestParsingFailed = 1 WHERE LoadTestRunId = @RunId

				---------------------------------------------------------------------------------
				-- Agent count and max user count
				---------------------------------------------------------------------------------
				UPDATE TSL_LoadTestCoreInfo	SET	AgentCount = (SELECT COUNT(AgentId) FROM LoadTestRunAgent WHERE LoadTestRunId = @RunId) WHERE LoadTestRunId = @RunId
				UPDATE TSL_LoadTestCoreInfo	SET	MaxUsers = (SELECT TOP 1 ComputedValue FROM LoadTestComputedCounterSample WHERE LoadTestRunId = @RunId 
					AND CategoryName = 'LoadTest:Scenario' AND CounterName = 'User Load' AND InstanceName = '_Total' ORDER BY ComputedValue DESC) WHERE LoadTestRunId = @RunId
				UPDATE TSL_LoadTestCoreInfo	SET	SampleInterval = (SELECT DATEDIFF(second,IntervalStartTime ,IntervalEndTime) FROM LoadTestRunInterval 
					WHERE TestRunIntervalId = 1 AND LoadTestRunId = @RunId) WHERE LoadTestRunId = @RunId
				---------------------------------------------------------------------------------
				-- Test Count and Avg test time
				---------------------------------------------------------------------------------
				BEGIN

					UPDATE TSL_LoadTestCoreInfo	SET TotalTests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
						AND CounterName = 'Total Tests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET PassedTests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
						AND CounterName = 'Passed Tests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET FailedTests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
						AND CounterName = 'Failed Tests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET TestsSec_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
						AND CounterName = 'Tests/Sec' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET AvgTT_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
						AND CounterName = 'Avg. Test Time' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId
				END

				---------------------------------------------------------------------------------
				-- Transactional Count and Avg trans times
				---------------------------------------------------------------------------------
				BEGIN
				
					UPDATE TSL_LoadTestCoreInfo	SET TotalTrans = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Transaction'
						AND CounterName = 'Total Transactions' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET TransSec_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Transaction'
						AND CounterName = 'Transactions/Sec' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET AvgTrET_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Transaction'
						AND CounterName = 'Avg. Transaction Time' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET AvgTrRT_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Transaction'
						AND CounterName = 'Avg. Response Time' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

				END

				---------------------------------------------------------------------------------
				-- Page Count and Avg Page times
				---------------------------------------------------------------------------------
				BEGIN
					UPDATE TSL_LoadTestCoreInfo	SET TotalPages = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Page'
						AND CounterName = 'Total Pages' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET PagesSec_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Page'
						AND CounterName = 'Pages/Sec' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET AvgPgT_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Page'
						AND CounterName = 'Avg. Page Time' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId
				END
				
				---------------------------------------------------------------------------------
				-- Request Count and Avg Request times
				---------------------------------------------------------------------------------
				BEGIN
					UPDATE TSL_LoadTestCoreInfo	SET TotalRequests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Total Requests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET PassedRequests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Passed Requests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET FailedRequests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Failed Requests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET CachedRequests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Cached Requests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET ReqSec_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Requests/Sec' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET AvgReqT_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Avg. Response Time' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET AvgReqLen_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Avg. Content Length' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId
				END

				---------------------------------------------------------------------------------
				-- Test Rig efficiency counters
				---------------------------------------------------------------------------------
				BEGIN
					UPDATE TSL_LoadTestCoreInfo	SET PctTime_WebTest = (SELECT CAST(AVG(ComputedValue) AS Decimal(15,2)) FROM LoadTestComputedCounterSample	
						WHERE CategoryName = 'LoadTest:Test' AND CounterName = '% Time in WebTest code' AND InstanceName = '_Total' 
						AND LoadTestRunId = @RunId) WHERE LoadTestRunId = @RunId
							
					UPDATE TSL_LoadTestCoreInfo	SET PctTime_Rules = (SELECT CAST(AVG(ComputedValue) AS Decimal(15,2)) FROM LoadTestComputedCounterSample	
						WHERE CategoryName = 'LoadTest:Test' AND CounterName = '% Time in Rules' AND InstanceName = '_Total' 
						AND LoadTestRunId = @RunId) WHERE LoadTestRunId = @RunId
							
					UPDATE TSL_LoadTestCoreInfo	SET PctTime_LoadPlugin = (SELECT CAST(AVG(ComputedValue) AS Decimal(15,2)) FROM LoadTestComputedCounterSample	
						WHERE CategoryName = 'LoadTest:Test' AND CounterName = '% Time in LoadTestPlugin' AND InstanceName = '_Total' 
						AND LoadTestRunId = @RunId) WHERE LoadTestRunId = @RunId
				END			

				---------------------------------------------------------------------------------
				-- Flags
				---------------------------------------------------------------------------------
				BEGIN
					UPDATE TSL_LoadTestCoreInfo	SET FLAG_ContainsPages = (CASE (SELECT COUNT(PageId) FROM LoadTestPageSummaryData WHERE LoadTestRunId = @RunId)
							WHEN 0 THEN 0 ELSE 1 END) WHERE LoadTestRunId = @RunId
			
					UPDATE TSL_LoadTestCoreInfo	SET FLAG_ContainsTransactions = (CASE (SELECT COUNT(TransactionId) FROM LoadTestTransactionSummaryData  WHERE LoadTestRunId = @RunId)
							WHEN 0 THEN 0 ELSE 1 END) WHERE LoadTestRunId = @RunId
			
					UPDATE TSL_LoadTestCoreInfo	SET FLAG_ContainsDetailedTimingInfo = (CASE (SELECT COUNT(TestDetailId) FROM LoadTestTestDetail WHERE LoadTestRunId = @RunId)
							WHEN 0 THEN 0 ELSE 1 END) WHERE LoadTestRunId = @RunId
			
					UPDATE TSL_LoadTestCoreInfo	SET FLAG_TestAborted = (CASE (SELECT Outcome FROM LoadTestRun WHERE LoadTestRunId = @RunId)
							WHEN 'Completed' THEN 0 ELSE 1 END) WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET FLAG_ContainsStepLoadProfile = (CASE 
								(SELECT COUNT(DISTINCT ComputedValue) FROM LoadTestPerformanceCounterSample AS a
								INNER JOIN MTSL_View_PerfmonInstanceNamesAndIds AS b
								ON a.LoadTestRunId = b.LoadTestRunId
								AND a.InstanceId = b.InstanceId
								WHERE a.LoadTestRunId = @RunId AND b.CounterName = 'User Load' 
								AND b.Instance = '_Total' AND a.ComputedValue <> 0)
							WHEN 1 THEN 0 ELSE 1 END) WHERE LoadTestRunId = @RunId
			
					-- Parsing completed for the run, so do a final sanity check on the results, looking for a total test Qty > 0 OR a Duration > 0.
					-- If either are zero or NULL, then the results will be invalid. If not, then we're good to go.
					SET @Qty = (SELECT TotalTests FROM TSL_LoadTestCoreInfo WHERE LoadTestRunId = @RunId)
					SET @Duration = (SELECT RunDuration FROM LoadTestRun WHERE LoadTestRunId = @RunId)
					IF (@Qty IS NOT NULL AND @Qty > 0 AND @Duration > 0) 
					BEGIN
						UPDATE TSL_LoadTestCoreInfo	SET FLAG_LoadTestParsingFailed = (0) WHERE LoadTestRunId = @RunId
					END

				END

				---------------------------------------------------------------------------------
				-- Add a row to the logging table
				---------------------------------------------------------------------------------
				INSERT INTO TSL_DbChangeLogs (LoadTestRunId, ChangeTypeId, ChangeTime) VALUES (@RunId, 1, GetDate()) 

			FETCH NEXT FROM myCursor INTO @RunId
		END
		CLOSE myCursor
		DEALLOCATE myCursor

		EXEC TSL_prc_UpdateScoreCardMachineAvailableMBTable
		EXEC TSL_prc_UpdateScoreCardMachineCpuTable
	END
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_UpdateLoadTestCoreInfoTable TO PUBLIC
	GO


/*== TSL_prc_UpdateScoreCardMachineAvailableMBTable =========================================================================
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_UpdateScoreCardMachineAvailableMBTable AS	
	BEGIN
		---------------------------------------------------------------------------------
		DECLARE @RunId INT
		DECLARE myCursor CURSOR FOR
			SELECT LoadTestRunId FROM LoadTestRun 
			WHERE LoadTestRunId NOT IN (SELECT DISTINCT LoadTestRunId FROM TSL_ScoreCardMachineAvailableMB)
			AND Outcome != 'In Progress'
			
		OPEN myCursor
		FETCH NEXT FROM myCursor INTO @RunId
		WHILE @@FETCH_STATUS = 0
			BEGIN	
				---------------------------------------------------------------------------------
				-- Core Summary Info
				---------------------------------------------------------------------------------
				INSERT INTO TSL_ScoreCardMachineAvailableMB
					SELECT 
						a.LoadTestRunId 
						,a.MachineName
						,b.InstanceId
						,COUNT(a.ComputedValue) AS [Qty]
						,CAST(MIN(a.ComputedValue) AS Decimal(15,2)) AS [Min]
						,CAST(AVG(a.ComputedValue) AS Decimal(15,2)) AS [Avg]
						,CAST(MAX(a.ComputedValue) AS Decimal(15,2)) AS [Max]
					FROM LoadTestComputedCounterSample AS a
					INNER JOIN MTSL_View_PerfmonInstanceNamesAndIds AS b 
					ON a.LoadTestRunId = b.LoadTestRunId
						AND a.MachineName = b.MachineName 
						AND a.CategoryName = b.CategoryName
						AND a.CounterName = b.CounterName 
						WHERE a.CategoryName = 'Memory' 
						AND a.CounterName = 'Available MBytes' 
						AND a.InstanceName = 'systemdiagnosticsperfcounterlibsingleinstance'
						AND a.LoadTestRunId = @RunId
					GROUP BY a.LoadTestRunId, a.MachineName, b.InstanceId
					ORDER BY a.MachineName ASC

				---------------------------------------------------------------------------------
				-- Add a row to the logging table
				---------------------------------------------------------------------------------
				INSERT INTO TSL_DbChangeLogs (LoadTestRunId, ChangeTypeId, ChangeTime) VALUES (@RunId, 3, GetDate()) 

			FETCH NEXT FROM myCursor INTO @RunId
		END
		CLOSE myCursor
		DEALLOCATE myCursor
	END
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_UpdateScoreCardMachineAvailableMBTable TO PUBLIC
	GO


/*== TSL_prc_UpdateScoreCardMachineCpuTable =========================================================================
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_UpdateScoreCardMachineCpuTable AS	
	BEGIN
		---------------------------------------------------------------------------------
		DECLARE @RunId INT
		DECLARE myCursor CURSOR FOR
			SELECT LoadTestRunId FROM LoadTestRun 
			WHERE LoadTestRunId NOT IN (SELECT DISTINCT LoadTestRunId FROM TSL_ScoreCardMachineCPU)
			AND Outcome != 'In Progress'
			
		OPEN myCursor
		FETCH NEXT FROM myCursor INTO @RunId
		WHILE @@FETCH_STATUS = 0
			BEGIN	
				---------------------------------------------------------------------------------
				-- Core Summary Info
				---------------------------------------------------------------------------------
				
				INSERT INTO TSL_ScoreCardMachineCPU
					SELECT 
						a.LoadTestRunId 
						,a.MachineName
						,b.InstanceId
						,COUNT(a.ComputedValue) AS [Qty]
						,CAST(MIN(a.ComputedValue) AS Decimal(15,2)) AS [Min]
						,CAST(AVG(a.ComputedValue) AS Decimal(15,2)) AS [Avg]
						,CAST(MAX(a.ComputedValue) AS Decimal(15,2)) AS [Max]
					FROM LoadTestComputedCounterSample AS a
					INNER JOIN MTSL_View_PerfmonInstanceNamesAndIds AS b 
					ON a.LoadTestRunId = b.LoadTestRunId
						AND a.MachineName = b.MachineName 
						AND a.CategoryName = b.CategoryName
						AND a.CounterName = b.CounterName 
						AND a.InstanceName = b.Instance
					WHERE a.CategoryName = 'Processor' 
						AND a.CounterName = '% Processor Time' 
						AND a.InstanceName = '_Total'
						AND a.LoadTestRunId = @RunId
					GROUP BY a.LoadTestRunId, a.MachineName, b.InstanceId
					ORDER BY a.MachineName ASC

				---------------------------------------------------------------------------------
				-- Add a row to the logging table
				---------------------------------------------------------------------------------
				INSERT INTO TSL_DbChangeLogs (LoadTestRunId, ChangeTypeId, ChangeTime) VALUES (@RunId, 2, GetDate()) 

			FETCH NEXT FROM myCursor INTO @RunId
		END
		CLOSE myCursor
		DEALLOCATE myCursor
	END
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_UpdateScoreCardMachineCpuTable TO PUBLIC
	GO


/*== TSL_prc_DailyStatusRunList ==========================================================================
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_DailyStatusRunList
		@StartDate  nvarchar(255) = ''
		,@EndDate	nvarchar(255) = ''
		,@MaxRowsToReturn int = 50
	AS

	SELECT LoadTestRunId AS 'Run #', LoadTestName, StartTime, RunDuration, [Description] FROM LoadTestRun 
		  WHERE 
		  -- Add a second to cover the rounding of milliseconds stored in the DB
		  --AND 
		  (@StartDate = '' OR  @EndDate = '' OR
		   StartTime BETWEEN @StartDate AND DATEADD(second, 1, @EndDate))
		  AND Outcome != 'InProgress' 		    
		  ORDER BY LoadTestRunId DESC
	/*
		;WITH Aggs
		AS	
		(SELECT	
				a.LoadTestRunId
				,COUNT(a.TestDetailId) AS [Total Tests]
				,COUNT(b.TestDetailId) AS [Failed Tests]
				,CAST(AVG(a.ElapsedTime) AS Decimal(15,2)) AS [Response Time]
			FROM 
				dbo.LoadTestTestDetail AS a

			/* LEFT OUTER JOIN means that the left side (which is the TABLE above) will have all
			results returned regardless of the criteria for the JOIN. So in this case, the Total
			Tests, shows everything, but the Failed Tests (which is the same physical table, but 
			is joined with a filter (b.Outcome = 1). 
			*/			
			LEFT OUTER JOIN dbo.LoadTestTestDetail AS b
			ON a.LoadTestRunId = b.LoadTestRunId
			AND a.TestDetailId = b.TestDetailId
			AND b.Outcome = 1
		
			WHERE 
				a.InMeasurementInterval = 1 
			GROUP BY a.LoadTestRunId
			)

		SELECT TOP (@MaxRowsToReturn) a.LoadTestRunId
			  ,a.LoadTestName
			  ,a.Description
			  ,a.StartTime
			  ,a.RunDuration
			  ,b.[Total Tests]
			  ,b.[Failed Tests]
			  ,CAST(b.[Response Time] AS Decimal(15,2)) AS 'Test Response Time'
			  ,CAST((CAST(b.[Failed Tests] AS Decimal(15,2)) / b.[Total Tests]) * 100 AS Decimal(3,0)) AS 'Percent Failed'
			  --,CAST(c.CumulativeValue AS Decimal(15,2)) AS 'Page Response Time'
			  --,CAST(d.CumulativeValue AS Decimal(15,2)) AS 'Pages Per Second'
		  FROM LoadTestRun AS a
	  
		  INNER JOIN Aggs AS b
		  ON a.LoadTestRunId = b.LoadTestRunId
		  
		  --INNER JOIN LoadTestComputedCounterSummary AS c
		  --on a.LoadTestRunId = c.LoadTestRunId
		  --INNER JOIN LoadTestComputedCounterSummary AS d
		  --on a.LoadTestRunId = d.LoadTestRunId
	  
		  WHERE 
		  -- Add a second to cover the rounding of milliseconds stored in the DB
		  --AND 
		  (@StartDate = '' OR  @EndDate = '' OR
		   a.StartTime BETWEEN @StartDate AND DATEADD(second, 1, @EndDate))
		  AND 
		    (@TestNameContains IS NULL or a.LoadTestName LIKE @TestNameContains)
		  AND 
		    (@DescriptionContains IS NULL or a.Description LIKE @DescriptionContains)
		    
		  ORDER BY a.LoadTestRunId DESC

	*/	
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_DailyStatusRunList TO PUBLIC
	GO


